package com.aternity.geolocations.firebird;

import au.com.bytecode.opencsv.CSVWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import org.apache.commons.io.FileUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;

/**
 * @author ddd
 * @since 11/27/13 2:30 PM
 */
public class FirebirdExtractNames4Entities {

    Connection connection;
    JdbcTemplate jdbcTemplate;
    final File dataFile = new File("tableau.entity.names.csv");

    public FirebirdExtractNames4Entities() throws Exception {
        Class.forName("org.firebirdsql.jdbc.FBDriver");
        Properties props = new Properties();
        props.setProperty("user", "SYSDBA");
        props.setProperty("password", "masterkey");
        props.setProperty("encoding", "UNICODE_FSS");
        connection = DriverManager.getConnection("jdbc:firebirdsql:localhost/3050:C:/dev/geoloc/GEOCODING.fdb", props);
        jdbcTemplate = new JdbcTemplate(new SingleConnectionDataSource(connection, true));
        if (dataFile.exists()) {
            FileUtils.forceDelete(dataFile);
        }
    }

    public void processCountries() throws Exception {
        processEntities(SELECT_COUNTRIES, dataFile);
    }

    public void processStates() throws Exception {
        processEntities(SELECT_STATES, dataFile);
    }

    public void processCities() throws Exception {
        processEntities(SELECT_CITIES, dataFile);
    }

    public void processEntities(String query, File f) throws Exception {
        Map<Integer, BaseEntityData> id2data = new HashMap<>();
        final List<BaseEntityData> list = jdbcTemplate.query(query, new CountryRowMapper());
        for (BaseEntityData ed : list) {
            final BaseEntityData registeredData = id2data.get(ed.id);
            if (registeredData == null) {
                id2data.put(ed.id, ed);
            } else if (registeredData.locale != null && !"en_US".equals(registeredData.locale)) {
                System.out.println("who is better? " + ed.locale + "<>" + registeredData.locale);
            }
        }

        List<BaseEntityData> toBeSorted = new ArrayList<>(id2data.values());
        Collections.sort(toBeSorted, new Comparator<BaseEntityData>() {

            @Override
            public int compare(BaseEntityData o1, BaseEntityData o2) {
                return o1.name.compareTo(o2.name);
            }
        });

        try (CSVWriter csvWriter = new CSVWriter(new OutputStreamWriter(new FileOutputStream(f, true), "UTF-8"))) {
            for (BaseEntityData baseEntityData : toBeSorted) {
                csvWriter.writeNext(new String[]{Integer.toString(baseEntityData.id), baseEntityData.name});
            }
            csvWriter.flush();
        }
    }

    public static void main(String[] args) throws Exception {
        FirebirdExtractNames4Entities extractor = new FirebirdExtractNames4Entities();
        extractor.processCountries();
        extractor.processStates();
        extractor.processCities();
    }

    static class BaseEntityData {

        int id;
        String name;
        String locale;

        public BaseEntityData() {
        }
    }

    static class CountryRowMapper implements RowMapper<BaseEntityData> {

        @Override
        public BaseEntityData mapRow(ResultSet rs, int i) throws SQLException {
            BaseEntityData d = new BaseEntityData();
            d.id = rs.getInt("id");
            d.name = rs.getString("name");
            String locale = rs.getString("locale");
            if (!"null".equals(locale)) {
                d.locale = locale;
            }
            return d;
        }
    }

    static String SELECT_COUNTRIES
            = "select \n"
            + "        country.ID as id,\n"
            + "        countrysyn.\"Name\" as \"name\", \n"
            + "        countrysyn.\"Locale\" as \"locale\"\n"
            + "from \n"
            + "    \"Country\" country\n"
            + "    join \"CountrySynonyms\" countrysyn on country.ID=countrysyn.\"ParentID\"\n"
            + "  where\n"
            + "      (countrysyn.\"Locale\"='en_US' or countrysyn.\"Locale\" is NULL)\n"
            + "      and countrysyn.\"IsDisplayName\" = 1\n"
            + "order by\n"
            + "    id";

    static String SELECT_STATES
            = "select \n"
            + "        state.ID as id,\n"
            + "        statesyn.\"Name\" as \"name\", \n"
            + "        statesyn.\"Locale\" as \"locale\"\n"
            + "from \n"
            + "    \"State\" state\n"
            + "    join \"StateSynonyms\" statesyn on state.ID=statesyn.\"ParentID\"\n"
            + "  where\n"
            + "      (statesyn.\"Locale\"='en_US' or statesyn.\"Locale\" is NULL)\n"
            + "      and statesyn.\"IsDisplayName\" = 1\n"
            + "order by\n"
            + "    id";

    static String SELECT_CITIES
            = "select \n"
            + "        city.ID as id,\n"
            + "        citysyn.\"Name\" as \"name\", \n"
            + "        citysyn.\"Locale\" as \"locale\"\n"
            + "from \n"
            + "    \"City\" city\n"
            + "    join \"CitySynonyms\" citysyn on city.ID=citysyn.\"ParentID\"\n"
            + "  where\n"
            + "      (citysyn.\"Locale\"='en_US' or citysyn.\"Locale\" is NULL)\n"
            + "      and citysyn.\"IsDisplayName\" = 1\n"
            + "order by\n"
            + "    id";

}
